{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Control Language"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "在进行本文以下内容之前需要注意：\n",
    "- 你有一个MySQL数据库，并且已经启动。\n",
    "- 你有可以连接该数据库的用户名和密码\n",
    "- 你有一个有权限操作的database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 打开数据库连接"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "db = pymysql.connect(host='localhost',\n",
    "                    user='root',\n",
    "                    password='root',\n",
    "                    database='shop',\n",
    "                    charset='utf8mb4',\n",
    "                    cursorclass=pymysql.cursors.DictCursor)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 使用crusor()方法创建一个游标对象cursor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor = db.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 使用execute()方法执行SQL查询"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(\"SELECT VERSION()\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 使用fetchone()方法获取单条数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Database version: {'VERSION()': '8.0.22'}\n"
     ]
    }
   ],
   "source": [
    "data = cursor.fetchone()\n",
    "print(f\"Database version: {data}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 关闭数据库连接"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "db.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 创建数据库表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "()\n",
      "None\n",
      "0\n"
     ]
    }
   ],
   "source": [
    "import pymysql\n",
    "\n",
    "db = pymysql.connect(host='localhost',\n",
    "                    user='root',\n",
    "                    password='root',\n",
    "                    database='shop',\n",
    "                    charset='utf8mb4',\n",
    "                    cursorclass=pymysql.cursors.DictCursor)\n",
    "\n",
    "cursor = db.cursor()\n",
    "\n",
    "cursor.execute(\"DROP TABLE IF EXISTS Employee \")\n",
    "\n",
    "sql = \"\"\"CREATE TABLE Employee (id INTEGER AUTO_INCREMENT,\n",
    "        first_name CHAR(20) NOT NULL,\n",
    "        last_name CHAR(20),\n",
    "        age INTEGER,\n",
    "        sex CHAR(1),\n",
    "        income FLOAT,\n",
    "        PRIMARY KEY (id))\"\"\"\n",
    "\n",
    "cursor.execute(sql)\n",
    "\n",
    "\n",
    "print(cursor.fetchall())\n",
    "print(cursor.description)\n",
    "print(cursor.execute(\"SELECT * FROM Employee\"))\n",
    "\n",
    "db.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据库插入操作"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{'age': 20,\n",
      "  'first_name': 'Mac',\n",
      "  'id': 1,\n",
      "  'income': 2000.0,\n",
      "  'last_name': 'Mohan',\n",
      "  'sex': 'M'}]\n"
     ]
    }
   ],
   "source": [
    "import pymysql\n",
    "from pprint import pprint\n",
    "\n",
    "db = pymysql.connect(host='localhost',\n",
    "                    user='root',\n",
    "                    password='root',\n",
    "                    database='shop',\n",
    "                    charset='utf8mb4',\n",
    "                    cursorclass=pymysql.cursors.DictCursor)\n",
    "\n",
    "\n",
    "cursor = db.cursor()\n",
    "\n",
    "sql = \"\"\"INSERT INTO Employee (first_name, last_name,age, sex, income)\n",
    "                    VALUES     (\"Mac\", \"Mohan\", 20, \"M\", 2000)\"\"\"\n",
    "\n",
    "try:\n",
    "    cursor.execute(sql)\n",
    "    cursor.execute(\"DELETE FROM Employee WHERE id BETWEEN 2 AND 100\")\n",
    "    db.commit()\n",
    "    cursor.execute(\"SELECT * FROM Employee\")\n",
    "    pprint(cursor.fetchall())\n",
    "except Exception as e:\n",
    "    db.rollback()\n",
    "    print(f\"db.rollback: {str(e)}\")\n",
    "    \n",
    "db.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据库查询操作"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 Mac Mohan 22 M 2000.0\n"
     ]
    }
   ],
   "source": [
    "import pymysql\n",
    "\n",
    "db = pymysql.connect(host='localhost',\n",
    "                    user='root',\n",
    "                    password='root',\n",
    "                    database='shop',\n",
    "                    charset='utf8mb4',\n",
    "                    cursorclass=pymysql.cursors.DictCursor)\n",
    "\n",
    "cursor = db.cursor()\n",
    "\n",
    "sql = \"\"\"SELECT * FROM Employee WHERE income > 1000 \"\"\"\n",
    "\n",
    "try:\n",
    "    cursor.execute(sql)\n",
    "    results = cursor.fetchall()\n",
    "    for row in results:\n",
    "        print(row[\"id\"], row[\"first_name\"], row[\"last_name\"],\n",
    "              row[\"age\"], row[\"sex\"], row[\"income\"])\n",
    "except Exception as e:\n",
    "    print(str(e))\n",
    "\n",
    "db.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据库更新操作"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "((1, 'Mac', 'Mohan', 22, 'M', 2000.0),)\n"
     ]
    }
   ],
   "source": [
    "import pymysql\n",
    "\n",
    "db = pymysql.connect(host='localhost',\n",
    "                    user='root',\n",
    "                    password='root',\n",
    "                    database='shop',\n",
    "                    charset='utf8mb4')\n",
    "\n",
    "cursor = db.cursor()\n",
    "\n",
    "\n",
    "sql = \"\"\"UPDATE Employee SET age = age + 1\n",
    "            WHERE SEX = 'M'\"\"\"\n",
    "\n",
    "try:\n",
    "    cursor.execute(sql)\n",
    "    \n",
    "    db.commit()\n",
    "    \n",
    "    cursor.execute(\"SELECT * FROM Employee\")\n",
    "    print(cursor.fetchall())\n",
    "except Exception as e:\n",
    "    db.rollback()\n",
    "    print(f\"db.rollback(): {str(e)}\")\n",
    "    \n",
    "db.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 删除操作"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "0\n"
     ]
    }
   ],
   "source": [
    "import pymysql\n",
    "\n",
    "db = pymysql.connect(host='localhost',\n",
    "                    user='root',\n",
    "                    password='root',\n",
    "                    database='shop',\n",
    "                    charset='utf8mb4')\n",
    "\n",
    "cursor = db.cursor()\n",
    "\n",
    "sql = \"\"\"DELETE FROM Employee WHERE age > 20\"\"\"\n",
    "\n",
    "try:\n",
    "    cursor.execute(sql)\n",
    "    print(cursor.execute(\"SELECT * FROM Employee\"))\n",
    "    db.commit()\n",
    "    print(cursor.execute(\"SELECT * FROM Employee\"))\n",
    "except Exception as e:\n",
    "    db.rollback()\n",
    "    print(f\"db.rollback(): {str(e)}\")\n",
    "\n",
    "db.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 执行事务"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "事务机制可以确保数据一致性。\n",
    "\n",
    "事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。\n",
    "\n",
    "- 原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。\n",
    "\n",
    "- 一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。\n",
    "\n",
    "- 隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。\n",
    "\n",
    "- 持久性（durability）。持续性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。\n",
    "\n",
    "\n",
    "对于支持事务的数据库， 在Python数据库编程中，当游标建立之时，就自动开始了一个隐形的数据库事务。\n",
    "\n",
    "commit()方法游标的所有更新操作，rollback（）方法回滚当前游标的所有操作。每一个方法都开始了一个新的事务。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql\n",
    "\n",
    "db = pymysql.connect(host='localhost',\n",
    "                    user='root',\n",
    "                    password='root',\n",
    "                    database='shop',\n",
    "                    charset='utf8mb4')\n",
    "\n",
    "# SQL删除记录语句\n",
    "sql = \"DELETE FROM EMPLOYEE WHERE AGE > %s\" % (20)\n",
    "try:\n",
    "   # 执行SQL语句\n",
    "   cursor.execute(sql)\n",
    "   # 向数据库提交\n",
    "   db.commit()\n",
    "except:\n",
    "   # 发生错误时回滚\n",
    "   db.rollback()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 基本操作"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 获取插入数据的ID(关联操作时会用到)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "16\n"
     ]
    }
   ],
   "source": [
    "import pymysql\n",
    "\n",
    "conn = pymysql.connect(host='127.0.0.1',\n",
    "                      port=3306,\n",
    "                      user='root',\n",
    "                      password='root',\n",
    "                       database='shop',\n",
    "                      charset='utf8mb4')\n",
    "\n",
    "cursor = conn.cursor()\n",
    "\n",
    "sql = \"\"\"INSERT INTO Employee (first_name, last_name,age, sex, income)\n",
    "                    VALUES    (%s, %s, %s, %s, %s)\"\"\"\n",
    "try:\n",
    "    cursor.execute(sql, [\"Dillon\", \"Leung\", 20, \"M\", 3000])\n",
    "    \n",
    "    conn.commit()\n",
    "    \n",
    "    last_id = cursor.lastrowid\n",
    "    print(last_id)\n",
    "except Exception as e:\n",
    "    conn.rollback()\n",
    "    print(f\"conn.rollback(): {str(e)}\")\n",
    "    \n",
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 批量执行"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "((15, 'Dillon', 'Leung', 20, 'M', 3000.0), (16, 'Dillon', 'Leung', 20, 'M', 3000.0), (17, 'Dillon', 'Leung', 20, 'M', 4000.0), (18, 'Dillon', 'Leung', 20, 'M', 5000.0), (19, 'Dillon', 'Leung', 20, 'M', 6000.0))\n"
     ]
    }
   ],
   "source": [
    "import pymysql\n",
    "\n",
    "conn = pymysql.connect(host='127.0.0.1',\n",
    "                      port=3306,\n",
    "                      user='root',\n",
    "                      password='root',\n",
    "                      database='shop',\n",
    "                      charset='utf8mb4')\n",
    "\n",
    "cursor = conn.cursor()\n",
    "\n",
    "sql = \"\"\"INSERT INTO Employee (first_name, last_name,age, sex, income)\n",
    "                    VALUES    (%s, %s, %s, %s, %s)\"\"\"\n",
    "\n",
    "\n",
    "data = [(\"Dillon\", \"Leung\", 20, \"M\", 4000),\n",
    "        (\"Dillon\", \"Leung\", 20, \"M\", 5000),\n",
    "        (\"Dillon\", \"Leung\", 20, \"M\", 6000)]\n",
    "\n",
    "try:\n",
    "    cursor.executemany(sql, data)\n",
    "    \n",
    "    conn.commit()\n",
    "    \n",
    "    cursor.execute(\"SELECT * FROM Employee\")\n",
    "    print(cursor.fetchall())\n",
    "except Exception as e:\n",
    "    conn.rollback()\n",
    "    print(f\"conn.rollback(): {str(e)}\")\n",
    "\n",
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 删"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "((17, 'Dillon', 'Leung', 20, 'M', 4000.0), (18, 'Dillon', 'Leung', 20, 'M', 5000.0), (19, 'Dillon', 'Leung', 20, 'M', 6000.0))\n"
     ]
    }
   ],
   "source": [
    "import pymysql\n",
    "\n",
    "conn = pymysql.connect(host='127.0.0.1',\n",
    "                      port=3306,\n",
    "                      user='root',\n",
    "                      password='root',\n",
    "                      database='shop',\n",
    "                      charset='utf8mb4')\n",
    "cursor = conn.cursor()\n",
    "\n",
    "sql = \"\"\"DELETE FROM Employee WHERE id=%s\"\"\"\n",
    "\n",
    "try:\n",
    "    cursor.executemany(sql, [15,16])\n",
    "    cursor.execute(\"SELECT * FROM Employee\")\n",
    "    \n",
    "    print(cursor.fetchall())\n",
    "    conn.commit()\n",
    "except Exception as e:\n",
    "    conn.rollback()\n",
    "    print(f\"conn.rollback(): {str(e)}\")\n",
    "    \n",
    "    \n",
    "# cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 查"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "id first_name last_name age sex income\n",
      "17 Dillon Leung 20 M 4000.0\n",
      "18 Dillon Leung 20 M 5000.0\n",
      "19 Dillon Leung 20 M 6000.0\n"
     ]
    }
   ],
   "source": [
    "import pymysql\n",
    "\n",
    "db = pymysql.connect(host='localhost',\n",
    "                    port=3306,\n",
    "                    user='root',\n",
    "                    password='root',\n",
    "                    database='shop',\n",
    "                    charset='utf8mb4')\n",
    "cursor = db.cursor()\n",
    "\n",
    "sql = \"\"\"SELECT * FROM Employee\"\"\"\n",
    "\n",
    "try:\n",
    "    cursor.execute(sql)\n",
    "    results = cursor.fetchall()\n",
    "    \n",
    "    print(\"id\", \"first_name\", \"last_name\",\"age\", \"sex\", \"income\")\n",
    "    for row in results:\n",
    "        print(row[0], row[1], row[2], row[3], row[4], row[5] )\n",
    "except Exception as e:\n",
    "    db.rollback()\n",
    "    print(f\"db.rollback(): {str(e)}\")\n",
    "\n",
    "finally:\n",
    "    cursor.close()\n",
    "    db.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 查询单条数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(19, 20)\n"
     ]
    }
   ],
   "source": [
    "import pymysql\n",
    "\n",
    "conn = pymysql.connect(host='localhost',\n",
    "                      port=3306,\n",
    "                      user='root',\n",
    "                      password='root',\n",
    "                      database='shop',\n",
    "                      charset='utf8mb4')\n",
    "\n",
    "cur = conn.cursor()\n",
    "\n",
    "\n",
    "sql = \"\"\"SELECT id, age FROM Employee WHERE id=19;\"\"\"\n",
    "try:\n",
    "    cur.execute(sql)\n",
    "    print(cur.fetchone())\n",
    "except Exception as e:\n",
    "    print(str(e))\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 查询多条数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "((17, 20), (18, 20), (19, 20))\n"
     ]
    }
   ],
   "source": [
    "import pymysql\n",
    "\n",
    "conn = pymysql.connect(host='localhost',\n",
    "                      port=3306,\n",
    "                      user='root',\n",
    "                      password='root',\n",
    "                      database='shop',\n",
    "                      charset='utf8mb4')\n",
    "\n",
    "cur = conn.cursor()\n",
    "\n",
    "sql = \"\"\"SELECT id, age FROM Employee;\"\"\"\n",
    "\n",
    "cur.execute(sql)\n",
    "print(cur.fetchall())\n",
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 进阶用法"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "((17, 'Dillon', 'Leung', 20, 'M', 4000.0), (18, 'Dillon', 'Leung', 20, 'M', 5000.0), (19, 'Dillon', 'Leung', 20, 'M', 6000.0))\n"
     ]
    }
   ],
   "source": [
    "import pymysql\n",
    "\n",
    "conn = pymysql.connect(host='localhost',\n",
    "                      port=3306,\n",
    "                      user='root',\n",
    "                      password='root',\n",
    "                      database='shop',\n",
    "                      charset='utf8mb4')\n",
    "\n",
    "cur = conn.cursor()\n",
    "\n",
    "cur.execute(\"SELECT * FROM Employee\")\n",
    "\n",
    "print(cur.fetchmany(3))\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.scroll(1, mode=\"absolute\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.fetchone()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(19, 'Dillon', 'Leung', 20, 'M', 6000.0)"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 光标按绝对位置移动2\n",
    "cur.scroll(2, mode=\"absolute\")\n",
    "cur.fetchone()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 模块函数和常量"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[41m                     classess                     \u001b[0m\n",
      "\u001b[41m001\u001b[0m Connect                       \u001b[41m002\u001b[0m Connection                    \u001b[41m003\u001b[0m DBAPISet                      \u001b[41m004\u001b[0m DataError                     \n",
      "\u001b[41m005\u001b[0m DatabaseError                 \u001b[41m006\u001b[0m Date                          \u001b[41m007\u001b[0m Error                         \u001b[41m008\u001b[0m IntegrityError                \n",
      "\u001b[41m009\u001b[0m InterfaceError                \u001b[41m010\u001b[0m InternalError                 \u001b[41m011\u001b[0m MySQLError                    \u001b[41m012\u001b[0m NotSupportedError             \n",
      "\u001b[41m013\u001b[0m OperationalError              \u001b[41m014\u001b[0m ProgrammingError              \u001b[41m015\u001b[0m Time                          \u001b[41m016\u001b[0m Timestamp                     \n",
      "\u001b[41m017\u001b[0m Warning                       \u001b[41m018\u001b[0m connect                       \n",
      "\u001b[41m               function_or_methods                \u001b[0m\n",
      "\u001b[41m001\u001b[0m Binary                        \u001b[41m002\u001b[0m DateFromTicks                 \u001b[41m003\u001b[0m TimeFromTicks                 \u001b[41m004\u001b[0m TimestampFromTicks            \n",
      "\u001b[41m005\u001b[0m get_client_info               \u001b[41m006\u001b[0m install_as_MySQLdb            \u001b[41m007\u001b[0m thread_safe                   \n",
      "\u001b[41m                    constants                     \u001b[0m\n",
      "\u001b[41m001\u001b[0m BINARY                        \u001b[41m002\u001b[0m DATE                          \u001b[41m003\u001b[0m DATETIME                      \u001b[41m004\u001b[0m NULL                          \n",
      "\u001b[41m005\u001b[0m NUMBER                        \u001b[41m006\u001b[0m ROWID                         \u001b[41m007\u001b[0m STRING                        \u001b[41m008\u001b[0m TIME                          \n",
      "\u001b[41m009\u001b[0m TIMESTAMP                     \u001b[41m010\u001b[0m VERSION                       \u001b[41m011\u001b[0m VERSION_STRING                \u001b[41m012\u001b[0m apilevel                      \n",
      "\u001b[41m013\u001b[0m paramstyle                    \u001b[41m014\u001b[0m threadsafety                  \u001b[41m015\u001b[0m version_info                  \n",
      "\n"
     ]
    }
   ],
   "source": [
    "aprint(classify(pymysql))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[41mpymysql\u001b[0m \n",
      "PyMySQL: A pure-Python MySQL client library.\n",
      "\n",
      "Copyright (c) 2010-2016 PyMySQL contributors\n",
      "\n",
      "Permission is hereby granted, free of charge, to any person obtaining a copy\n",
      "of this software and associated documentation files (the \"Software\"), to deal\n",
      "in the Software without restriction, including without limitation the rights\n",
      "to use, copy, modify, merge, publish, distribute, sublicense, and/or sell\n",
      "copies of the Software, and to permit persons to whom the Software is\n",
      "furnished to do so, subject to the following conditions:\n",
      "\n",
      "The above copyright notice and this permission notice shall be included in\n",
      "all copies or substantial portions of the Software.\n",
      "\n",
      "THE SOFTWARE IS PROVIDED \"AS IS\", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR\n",
      "IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,\n",
      "FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE\n",
      "AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER\n",
      "LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,\n",
      "OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN\n",
      "THE SOFTWARE.\n",
      "\n",
      "\u001b[41m                     classess                     \u001b[0m\n",
      "\u001b[42mConnect\u001b[0m\n",
      "\n",
      "        Representation of a socket with a mysql server.\n",
      "\n",
      "        The proper way to get an instance of this class is to call\n",
      "        connect().\n",
      "\n",
      "        Establish a connection to the MySQL database. Accepts several\n",
      "        arguments:\n",
      "\n",
      "        :param host: Host where the database server is located\n",
      "        :param user: Username to log in as\n",
      "        :param password: Password to use.\n",
      "        :param database: Database to use, None to not use a particular one.\n",
      "        :param port: MySQL port to use, default is usually OK. (default: 3306)\n",
      "        :param bind_address: When the client has multiple network interfaces, specify\n",
      "            the interface from which to connect to the host. Argument can be\n",
      "            a hostname or an IP address.\n",
      "        :param unix_socket: Optionally, you can use a unix socket rather than TCP/IP.\n",
      "        :param read_timeout: The timeout for reading from the connection in seconds (default: None - no timeout)\n",
      "        :param write_timeout: The timeout for writing to the connection in seconds (default: None - no timeout)\n",
      "        :param charset: Charset you want to use.\n",
      "        :param sql_mode: Default SQL_MODE to use.\n",
      "        :param read_default_file:\n",
      "            Specifies  my.cnf file to read these parameters from under the [client] section.\n",
      "        :param conv:\n",
      "            Conversion dictionary to use instead of the default one.\n",
      "            This is used to provide custom marshalling and unmarshalling of types.\n",
      "            See converters.\n",
      "        :param use_unicode:\n",
      "            Whether or not to default to unicode strings.\n",
      "            This option defaults to true.\n",
      "        :param client_flag: Custom flags to send to MySQL. Find potential values in constants.CLIENT.\n",
      "        :param cursorclass: Custom cursor class to use.\n",
      "        :param init_command: Initial SQL statement to run when connection is established.\n",
      "        :param connect_timeout: Timeout before throwing an exception when connecting.\n",
      "            (default: 10, min: 1, max: 31536000)\n",
      "        :param ssl:\n",
      "            A dict of arguments similar to mysql_ssl_set()'s parameters.\n",
      "        :param ssl_ca: Path to the file that contains a PEM-formatted CA certificate\n",
      "        :param ssl_cert: Path to the file that contains a PEM-formatted client certificate\n",
      "        :param ssl_disabled: A boolean value that disables usage of TLS\n",
      "        :param ssl_key: Path to the file that contains a PEM-formatted private key for the client certificate\n",
      "        :param ssl_verify_cert: Set to true to check the validity of server certificates\n",
      "        :param ssl_verify_identity: Set to true to check the server's identity\n",
      "        :param read_default_group: Group to read from in the configuration file.\n",
      "        :param autocommit: Autocommit mode. None means use server default. (default: False)\n",
      "        :param local_infile: Boolean to enable the use of LOAD DATA LOCAL command. (default: False)\n",
      "        :param max_allowed_packet: Max size of packet sent to server in bytes. (default: 16MB)\n",
      "            Only used to limit size of \"LOAD LOCAL INFILE\" data packet smaller than default (16KB).\n",
      "        :param defer_connect: Don't explicitly connect on construction - wait for connect call.\n",
      "            (default: False)\n",
      "        :param auth_plugin_map: A dict of plugin names to a class that processes that plugin.\n",
      "            The class will take the Connection object as the argument to the constructor.\n",
      "            The class needs an authenticate method taking an authentication packet as\n",
      "            an argument.  For the dialog plugin, a prompt(echo, prompt) method can be used\n",
      "            (if no authenticate method) for returning a string from the user. (experimental)\n",
      "        :param server_public_key: SHA256 authentication plugin public key value. (default: None)\n",
      "        :param binary_prefix: Add _binary prefix on bytes and bytearray. (default: False)\n",
      "        :param compress: Not supported\n",
      "        :param named_pipe: Not supported\n",
      "        :param db: **DEPRECATED** Alias for database.\n",
      "        :param passwd: **DEPRECATED** Alias for password.\n",
      "\n",
      "        See `Connection <https://www.python.org/dev/peps/pep-0249/#connection-objects>`_ in the\n",
      "        specification.\n",
      "    \n",
      "\u001b[42mConnection\u001b[0m\n",
      "\n",
      "        Representation of a socket with a mysql server.\n",
      "\n",
      "        The proper way to get an instance of this class is to call\n",
      "        connect().\n",
      "\n",
      "        Establish a connection to the MySQL database. Accepts several\n",
      "        arguments:\n",
      "\n",
      "        :param host: Host where the database server is located\n",
      "        :param user: Username to log in as\n",
      "        :param password: Password to use.\n",
      "        :param database: Database to use, None to not use a particular one.\n",
      "        :param port: MySQL port to use, default is usually OK. (default: 3306)\n",
      "        :param bind_address: When the client has multiple network interfaces, specify\n",
      "            the interface from which to connect to the host. Argument can be\n",
      "            a hostname or an IP address.\n",
      "        :param unix_socket: Optionally, you can use a unix socket rather than TCP/IP.\n",
      "        :param read_timeout: The timeout for reading from the connection in seconds (default: None - no timeout)\n",
      "        :param write_timeout: The timeout for writing to the connection in seconds (default: None - no timeout)\n",
      "        :param charset: Charset you want to use.\n",
      "        :param sql_mode: Default SQL_MODE to use.\n",
      "        :param read_default_file:\n",
      "            Specifies  my.cnf file to read these parameters from under the [client] section.\n",
      "        :param conv:\n",
      "            Conversion dictionary to use instead of the default one.\n",
      "            This is used to provide custom marshalling and unmarshalling of types.\n",
      "            See converters.\n",
      "        :param use_unicode:\n",
      "            Whether or not to default to unicode strings.\n",
      "            This option defaults to true.\n",
      "        :param client_flag: Custom flags to send to MySQL. Find potential values in constants.CLIENT.\n",
      "        :param cursorclass: Custom cursor class to use.\n",
      "        :param init_command: Initial SQL statement to run when connection is established.\n",
      "        :param connect_timeout: Timeout before throwing an exception when connecting.\n",
      "            (default: 10, min: 1, max: 31536000)\n",
      "        :param ssl:\n",
      "            A dict of arguments similar to mysql_ssl_set()'s parameters.\n",
      "        :param ssl_ca: Path to the file that contains a PEM-formatted CA certificate\n",
      "        :param ssl_cert: Path to the file that contains a PEM-formatted client certificate\n",
      "        :param ssl_disabled: A boolean value that disables usage of TLS\n",
      "        :param ssl_key: Path to the file that contains a PEM-formatted private key for the client certificate\n",
      "        :param ssl_verify_cert: Set to true to check the validity of server certificates\n",
      "        :param ssl_verify_identity: Set to true to check the server's identity\n",
      "        :param read_default_group: Group to read from in the configuration file.\n",
      "        :param autocommit: Autocommit mode. None means use server default. (default: False)\n",
      "        :param local_infile: Boolean to enable the use of LOAD DATA LOCAL command. (default: False)\n",
      "        :param max_allowed_packet: Max size of packet sent to server in bytes. (default: 16MB)\n",
      "            Only used to limit size of \"LOAD LOCAL INFILE\" data packet smaller than default (16KB).\n",
      "        :param defer_connect: Don't explicitly connect on construction - wait for connect call.\n",
      "            (default: False)\n",
      "        :param auth_plugin_map: A dict of plugin names to a class that processes that plugin.\n",
      "            The class will take the Connection object as the argument to the constructor.\n",
      "            The class needs an authenticate method taking an authentication packet as\n",
      "            an argument.  For the dialog plugin, a prompt(echo, prompt) method can be used\n",
      "            (if no authenticate method) for returning a string from the user. (experimental)\n",
      "        :param server_public_key: SHA256 authentication plugin public key value. (default: None)\n",
      "        :param binary_prefix: Add _binary prefix on bytes and bytearray. (default: False)\n",
      "        :param compress: Not supported\n",
      "        :param named_pipe: Not supported\n",
      "        :param db: **DEPRECATED** Alias for database.\n",
      "        :param passwd: **DEPRECATED** Alias for password.\n",
      "\n",
      "        See `Connection <https://www.python.org/dev/peps/pep-0249/#connection-objects>`_ in the\n",
      "        specification.\n",
      "    \n",
      "\u001b[42mDBAPISet\u001b[0m\n",
      "\n",
      "\u001b[42mDataError\u001b[0m\n",
      "    Exception raised for errors that are due to problems with the\n",
      "        processed data like division by zero, numeric value out of range,\n",
      "        etc.\n",
      "\u001b[42mDatabaseError\u001b[0m\n",
      "    Exception raised for errors that are related to the\n",
      "        database.\n",
      "\u001b[42mDate\u001b[0m\n",
      "    date(year, month, day) --> date object\n",
      "\u001b[42mError\u001b[0m\n",
      "    Exception that is the base class of all other error exceptions\n",
      "        (not Warning).\n",
      "\u001b[42mIntegrityError\u001b[0m\n",
      "    Exception raised when the relational integrity of the database\n",
      "        is affected, e.g. a foreign key check fails, duplicate key,\n",
      "        etc.\n",
      "\u001b[42mInterfaceError\u001b[0m\n",
      "    Exception raised for errors that are related to the database\n",
      "        interface rather than the database itself.\n",
      "\u001b[42mInternalError\u001b[0m\n",
      "    Exception raised when the database encounters an internal\n",
      "        error, e.g. the cursor is not valid anymore, the transaction is\n",
      "        out of sync, etc.\n",
      "\u001b[42mMySQLError\u001b[0m\n",
      "    Exception related to operation with MySQL.\n",
      "\u001b[42mNotSupportedError\u001b[0m\n",
      "    Exception raised in case a method or database API was used\n",
      "        which is not supported by the database, e.g. requesting a\n",
      "        .rollback() on a connection that does not support transaction or\n",
      "        has transactions turned off.\n",
      "\u001b[42mOperationalError\u001b[0m\n",
      "    Exception raised for errors that are related to the database's\n",
      "        operation and not necessarily under the control of the programmer,\n",
      "        e.g. an unexpected disconnect occurs, the data source name is not\n",
      "        found, a transaction could not be processed, a memory allocation\n",
      "        error occurred during processing, etc.\n",
      "\u001b[42mProgrammingError\u001b[0m\n",
      "    Exception raised for programming errors, e.g. table not found\n",
      "        or already exists, syntax error in the SQL statement, wrong number\n",
      "        of parameters specified, etc.\n",
      "\u001b[42mTime\u001b[0m\n",
      "    time([hour[, minute[, second[, microsecond[, tzinfo]]]]]) --> a time object\n",
      "\n",
      "    All arguments are optional. tzinfo may be None, or an instance of\n",
      "    a tzinfo subclass. The remaining arguments may be ints.\n",
      "\n",
      "\u001b[42mTimestamp\u001b[0m\n",
      "    datetime(year, month, day[, hour[, minute[, second[, microsecond[,tzinfo]]]]])\n",
      "\n",
      "    The year, month and day arguments are required. tzinfo may be None, or an\n",
      "    instance of a tzinfo subclass. The remaining arguments may be ints.\n",
      "\n",
      "\u001b[42mWarning\u001b[0m\n",
      "    Exception raised for important warnings like data truncations\n",
      "        while inserting, etc.\n",
      "\u001b[42mconnect\u001b[0m\n",
      "\n",
      "        Representation of a socket with a mysql server.\n",
      "\n",
      "        The proper way to get an instance of this class is to call\n",
      "        connect().\n",
      "\n",
      "        Establish a connection to the MySQL database. Accepts several\n",
      "        arguments:\n",
      "\n",
      "        :param host: Host where the database server is located\n",
      "        :param user: Username to log in as\n",
      "        :param password: Password to use.\n",
      "        :param database: Database to use, None to not use a particular one.\n",
      "        :param port: MySQL port to use, default is usually OK. (default: 3306)\n",
      "        :param bind_address: When the client has multiple network interfaces, specify\n",
      "            the interface from which to connect to the host. Argument can be\n",
      "            a hostname or an IP address.\n",
      "        :param unix_socket: Optionally, you can use a unix socket rather than TCP/IP.\n",
      "        :param read_timeout: The timeout for reading from the connection in seconds (default: None - no timeout)\n",
      "        :param write_timeout: The timeout for writing to the connection in seconds (default: None - no timeout)\n",
      "        :param charset: Charset you want to use.\n",
      "        :param sql_mode: Default SQL_MODE to use.\n",
      "        :param read_default_file:\n",
      "            Specifies  my.cnf file to read these parameters from under the [client] section.\n",
      "        :param conv:\n",
      "            Conversion dictionary to use instead of the default one.\n",
      "            This is used to provide custom marshalling and unmarshalling of types.\n",
      "            See converters.\n",
      "        :param use_unicode:\n",
      "            Whether or not to default to unicode strings.\n",
      "            This option defaults to true.\n",
      "        :param client_flag: Custom flags to send to MySQL. Find potential values in constants.CLIENT.\n",
      "        :param cursorclass: Custom cursor class to use.\n",
      "        :param init_command: Initial SQL statement to run when connection is established.\n",
      "        :param connect_timeout: Timeout before throwing an exception when connecting.\n",
      "            (default: 10, min: 1, max: 31536000)\n",
      "        :param ssl:\n",
      "            A dict of arguments similar to mysql_ssl_set()'s parameters.\n",
      "        :param ssl_ca: Path to the file that contains a PEM-formatted CA certificate\n",
      "        :param ssl_cert: Path to the file that contains a PEM-formatted client certificate\n",
      "        :param ssl_disabled: A boolean value that disables usage of TLS\n",
      "        :param ssl_key: Path to the file that contains a PEM-formatted private key for the client certificate\n",
      "        :param ssl_verify_cert: Set to true to check the validity of server certificates\n",
      "        :param ssl_verify_identity: Set to true to check the server's identity\n",
      "        :param read_default_group: Group to read from in the configuration file.\n",
      "        :param autocommit: Autocommit mode. None means use server default. (default: False)\n",
      "        :param local_infile: Boolean to enable the use of LOAD DATA LOCAL command. (default: False)\n",
      "        :param max_allowed_packet: Max size of packet sent to server in bytes. (default: 16MB)\n",
      "            Only used to limit size of \"LOAD LOCAL INFILE\" data packet smaller than default (16KB).\n",
      "        :param defer_connect: Don't explicitly connect on construction - wait for connect call.\n",
      "            (default: False)\n",
      "        :param auth_plugin_map: A dict of plugin names to a class that processes that plugin.\n",
      "            The class will take the Connection object as the argument to the constructor.\n",
      "            The class needs an authenticate method taking an authentication packet as\n",
      "            an argument.  For the dialog plugin, a prompt(echo, prompt) method can be used\n",
      "            (if no authenticate method) for returning a string from the user. (experimental)\n",
      "        :param server_public_key: SHA256 authentication plugin public key value. (default: None)\n",
      "        :param binary_prefix: Add _binary prefix on bytes and bytearray. (default: False)\n",
      "        :param compress: Not supported\n",
      "        :param named_pipe: Not supported\n",
      "        :param db: **DEPRECATED** Alias for database.\n",
      "        :param passwd: **DEPRECATED** Alias for password.\n",
      "\n",
      "        See `Connection <https://www.python.org/dev/peps/pep-0249/#connection-objects>`_ in the\n",
      "        specification.\n",
      "    \n",
      "\u001b[41m               function_or_methods                \u001b[0m\n",
      "\u001b[42mBinary\u001b[0m\n",
      "    Return x as a binary type.\n",
      "\u001b[42mDateFromTicks\u001b[0m\n",
      "\n",
      "\u001b[42mTimeFromTicks\u001b[0m\n",
      "\n",
      "\u001b[42mTimestampFromTicks\u001b[0m\n",
      "\n",
      "\u001b[42mget_client_info\u001b[0m\n",
      "\n",
      "\u001b[42minstall_as_MySQLdb\u001b[0m\n",
      "\n",
      "        After this function is called, any application that imports MySQLdb or\n",
      "        _mysql will unwittingly actually use pymysql.\n",
      "    \n",
      "\u001b[42mthread_safe\u001b[0m\n",
      "\n",
      "\u001b[41m                    constants                     \u001b[0m\n",
      "\u001b[42mBINARY\u001b[0m\n",
      "    DBAPISet({249, 250, 251, 252})\n",
      "\u001b[42mDATE\u001b[0m\n",
      "    DBAPISet({10, 14})\n",
      "\u001b[42mDATETIME\u001b[0m\n",
      "    DBAPISet({12, 7})\n",
      "\u001b[42mNULL\u001b[0m\n",
      "    'NULL'\n",
      "\u001b[42mNUMBER\u001b[0m\n",
      "    DBAPISet({0, 1, 3, 4, 5, 8, 9, 13})\n",
      "\u001b[42mROWID\u001b[0m\n",
      "    DBAPISet()\n",
      "\u001b[42mSTRING\u001b[0m\n",
      "    DBAPISet({253, 254, 247})\n",
      "\u001b[42mTIME\u001b[0m\n",
      "    DBAPISet({11})\n",
      "\u001b[42mTIMESTAMP\u001b[0m\n",
      "    DBAPISet({12, 7})\n",
      "\u001b[42mVERSION\u001b[0m\n",
      "    (1, 0, 2, None)\n",
      "\u001b[42mVERSION_STRING\u001b[0m\n",
      "    '1.0.2'\n",
      "\u001b[42mapilevel\u001b[0m\n",
      "    '2.0'\n",
      "\u001b[42mparamstyle\u001b[0m\n",
      "    'pyformat'\n",
      "\u001b[42mthreadsafety\u001b[0m\n",
      "    1\n",
      "\u001b[42mversion_info\u001b[0m\n",
      "    (1, 4, 0, 'final', 0)\n",
      "None\n",
      "\n"
     ]
    }
   ],
   "source": [
    "aprint(adoc(pymysql))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# methods and attributes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "from attributesDir.attributesDir import adoc,aprint, classify"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql\n",
    "\n",
    "conn = pymysql.connect(host='localhost',\n",
    "                      port=3306,\n",
    "                      user='root',\n",
    "                      password='root',\n",
    "                      database='shop',\n",
    "                      charset='utf8mb4')\n",
    "\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## connect"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### classify(conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "If it is a callable object then you can assign a __name__ attribute to it.\n",
      "But the value assigned to __name__ must be the name of the callable object.\n",
      "If it is a callable object then you can assign a __name__ attribute to it.\n",
      "But the value assigned to __name__ must be the name of the callable object.\n",
      "If it is a callable object then you can assign a __name__ attribute to it.\n",
      "But the value assigned to __name__ must be the name of the callable object.\n",
      "\u001b[41m                     classess                     \u001b[0m\n",
      "\u001b[41m001\u001b[0m DataError                     \u001b[41m002\u001b[0m DatabaseError                 \u001b[41m003\u001b[0m Error                         \u001b[41m004\u001b[0m IntegrityError                \n",
      "\u001b[41m005\u001b[0m InterfaceError                \u001b[41m006\u001b[0m InternalError                 \u001b[41m007\u001b[0m NotSupportedError             \u001b[41m008\u001b[0m OperationalError              \n",
      "\u001b[41m009\u001b[0m ProgrammingError              \u001b[41m010\u001b[0m Warning                       \u001b[41m011\u001b[0m cursorclass                   \n",
      "\u001b[41m               function_or_methods                \u001b[0m\n",
      "\u001b[41m001\u001b[0m affected_rows                 \u001b[41m002\u001b[0m autocommit                    \u001b[41m003\u001b[0m begin                         \u001b[41m004\u001b[0m character_set_name            \n",
      "\u001b[41m005\u001b[0m close                         \u001b[41m006\u001b[0m commit                        \u001b[41m007\u001b[0m connect                       \u001b[41m008\u001b[0m cursor                        \n",
      "\u001b[41m009\u001b[0m escape                        \u001b[41m010\u001b[0m escape_string                 \u001b[41m011\u001b[0m get_autocommit                \u001b[41m012\u001b[0m get_host_info                 \n",
      "\u001b[41m013\u001b[0m get_proto_info                \u001b[41m014\u001b[0m get_server_info               \u001b[41m015\u001b[0m insert_id                     \u001b[41m016\u001b[0m kill                          \n",
      "\u001b[41m017\u001b[0m literal                       \u001b[41m018\u001b[0m next_result                   \u001b[41m019\u001b[0m ping                          \u001b[41m020\u001b[0m query                         \n",
      "\u001b[41m021\u001b[0m rollback                      \u001b[41m022\u001b[0m select_db                     \u001b[41m023\u001b[0m set_charset                   \u001b[41m024\u001b[0m show_warnings                 \n",
      "\u001b[41m025\u001b[0m thread_id                     \u001b[41m026\u001b[0m write_packet                  \n",
      "\u001b[41m                    constants                     \u001b[0m\n",
      "\u001b[41m001\u001b[0m autocommit_mode               \u001b[41m002\u001b[0m bind_address                  \u001b[41m003\u001b[0m charset                       \u001b[41m004\u001b[0m client_flag                   \n",
      "\u001b[41m005\u001b[0m connect_timeout               \u001b[41m006\u001b[0m db                            \u001b[41m007\u001b[0m decoders                      \u001b[41m008\u001b[0m encoders                      \n",
      "\u001b[41m009\u001b[0m encoding                      \u001b[41m010\u001b[0m host                          \u001b[41m011\u001b[0m host_info                     \u001b[41m012\u001b[0m init_command                  \n",
      "\u001b[41m013\u001b[0m max_allowed_packet            \u001b[41m014\u001b[0m open                          \u001b[41m015\u001b[0m password                      \u001b[41m016\u001b[0m port                          \n",
      "\u001b[41m017\u001b[0m protocol_version              \u001b[41m018\u001b[0m salt                          \u001b[41m019\u001b[0m server_capabilities           \u001b[41m020\u001b[0m server_charset                \n",
      "\u001b[41m021\u001b[0m server_language               \u001b[41m022\u001b[0m server_public_key             \u001b[41m023\u001b[0m server_status                 \u001b[41m024\u001b[0m server_thread_id              \n",
      "\u001b[41m025\u001b[0m server_version                \u001b[41m026\u001b[0m sql_mode                      \u001b[41m027\u001b[0m ssl                           \u001b[41m028\u001b[0m unix_socket                   \n",
      "\u001b[41m029\u001b[0m use_unicode                   \u001b[41m030\u001b[0m user                          \n",
      "\n"
     ]
    }
   ],
   "source": [
    "aprint(classify(conn))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "((17, 'Dillon', 'Leung', 20, 'M', 4000.0), (18, 'Dillon', 'Leung', 20, 'M', 5000.0), (19, 'Dillon', 'Leung', 20, 'M', 6000.0), (20, 'Dillon', 'Leung', 20, 'M', 4000.0), (21, 'Dillon', 'Leung', 20, 'M', 5000.0), (22, 'Dillon', 'Leung', 20, 'M', 6000.0), (23, 'Dillon', 'Leung', 20, 'M', 4000.0), (24, 'Dillon', 'Leung', 20, 'M', 5000.0), (25, 'Dillon', 'Leung', 20, 'M', 6000.0))\n"
     ]
    }
   ],
   "source": [
    "sql = \"\"\"INSERT INTO Employee (first_name, last_name,age, sex, income)\n",
    "                    VALUES    (%s, %s, %s, %s, %s)\"\"\"\n",
    "\n",
    "\n",
    "data = [(\"Dillon\", \"Leung\", 20, \"M\", 4000),\n",
    "        (\"Dillon\", \"Leung\", 20, \"M\", 5000),\n",
    "        (\"Dillon\", \"Leung\", 20, \"M\", 6000)]\n",
    "\n",
    "try:\n",
    "    cursor.executemany(sql, data)\n",
    "    \n",
    "    conn.commit()\n",
    "    \n",
    "    cursor.execute(\"SELECT * FROM Employee\")\n",
    "    print(cursor.fetchall())\n",
    "except Exception as e:\n",
    "    conn.rollback()\n",
    "    print(f\"conn.rollback(): {str(e)}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6"
      ]
     },
     "execution_count": 165,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.affected_rows()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.begin()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"'INSERT INTO Employee (first_name, last_name,age, sex, income)\\\\n                    VALUES    (%s, %s, %s, %s, %s)'\""
      ]
     },
     "execution_count": 169,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.escape(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'INSERT INTO Employee (first_name, last_name,age, sex, income)\\n                    VALUES    (%s, %s, %s, %s, %s)'"
      ]
     },
     "execution_count": 170,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'INSERT INTO Employee (first_name, last_name,age, sex, income)\\\\n                    VALUES    (%s, %s, %s, %s, %s)'"
      ]
     },
     "execution_count": 171,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.escape_string(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 172,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.get_autocommit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'socket localhost:3306'"
      ]
     },
     "execution_count": 173,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.get_host_info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10"
      ]
     },
     "execution_count": 174,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.get_proto_info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'8.0.22'"
      ]
     },
     "execution_count": 175,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.get_server_info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 176,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.insert_id()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.kill()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"'INSERT INTO Employee (first_name, last_name,age, sex, income)\\\\n                    VALUES    (%s, %s, %s, %s, %s)'\""
      ]
     },
     "execution_count": 177,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.literal(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.next_result()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.ping(reconnect=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "9"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.query(\"SELECT * FROM Employee\",unbuffered=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "9"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.affected_rows()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "116"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.thread_id()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.select_db('world')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(\"USE fin_mgt_ansys;\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(\"SHOW DATABASES;\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(('fin_mgt_ansys',),\n",
       " ('information_schema',),\n",
       " ('mysql',),\n",
       " ('performance_schema',),\n",
       " ('sakila',),\n",
       " ('shop',),\n",
       " ('sys',),\n",
       " ('world',))"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### adoc(conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "If it is a callable object then you can assign a __name__ attribute to it.\n",
      "But the value assigned to __name__ must be the name of the callable object.\n",
      "If it is a callable object then you can assign a __name__ attribute to it.\n",
      "But the value assigned to __name__ must be the name of the callable object.\n",
      "If it is a callable object then you can assign a __name__ attribute to it.\n",
      "But the value assigned to __name__ must be the name of the callable object.\n",
      "\u001b[41m<pymysql.connections.Connection object at 0x0000017A0E38FC50>\u001b[0m \n",
      "    Representation of a socket with a mysql server.\n",
      "\n",
      "    The proper way to get an instance of this class is to call\n",
      "    connect().\n",
      "\n",
      "    Establish a connection to the MySQL database. Accepts several\n",
      "    arguments:\n",
      "\n",
      "    :param host: Host where the database server is located\n",
      "    :param user: Username to log in as\n",
      "    :param password: Password to use.\n",
      "    :param database: Database to use, None to not use a particular one.\n",
      "    :param port: MySQL port to use, default is usually OK. (default: 3306)\n",
      "    :param bind_address: When the client has multiple network interfaces, specify\n",
      "        the interface from which to connect to the host. Argument can be\n",
      "        a hostname or an IP address.\n",
      "    :param unix_socket: Optionally, you can use a unix socket rather than TCP/IP.\n",
      "    :param read_timeout: The timeout for reading from the connection in seconds (default: None - no timeout)\n",
      "    :param write_timeout: The timeout for writing to the connection in seconds (default: None - no timeout)\n",
      "    :param charset: Charset you want to use.\n",
      "    :param sql_mode: Default SQL_MODE to use.\n",
      "    :param read_default_file:\n",
      "        Specifies  my.cnf file to read these parameters from under the [client] section.\n",
      "    :param conv:\n",
      "        Conversion dictionary to use instead of the default one.\n",
      "        This is used to provide custom marshalling and unmarshalling of types.\n",
      "        See converters.\n",
      "    :param use_unicode:\n",
      "        Whether or not to default to unicode strings.\n",
      "        This option defaults to true.\n",
      "    :param client_flag: Custom flags to send to MySQL. Find potential values in constants.CLIENT.\n",
      "    :param cursorclass: Custom cursor class to use.\n",
      "    :param init_command: Initial SQL statement to run when connection is established.\n",
      "    :param connect_timeout: Timeout before throwing an exception when connecting.\n",
      "        (default: 10, min: 1, max: 31536000)\n",
      "    :param ssl:\n",
      "        A dict of arguments similar to mysql_ssl_set()'s parameters.\n",
      "    :param ssl_ca: Path to the file that contains a PEM-formatted CA certificate\n",
      "    :param ssl_cert: Path to the file that contains a PEM-formatted client certificate\n",
      "    :param ssl_disabled: A boolean value that disables usage of TLS\n",
      "    :param ssl_key: Path to the file that contains a PEM-formatted private key for the client certificate\n",
      "    :param ssl_verify_cert: Set to true to check the validity of server certificates\n",
      "    :param ssl_verify_identity: Set to true to check the server's identity\n",
      "    :param read_default_group: Group to read from in the configuration file.\n",
      "    :param autocommit: Autocommit mode. None means use server default. (default: False)\n",
      "    :param local_infile: Boolean to enable the use of LOAD DATA LOCAL command. (default: False)\n",
      "    :param max_allowed_packet: Max size of packet sent to server in bytes. (default: 16MB)\n",
      "        Only used to limit size of \"LOAD LOCAL INFILE\" data packet smaller than default (16KB).\n",
      "    :param defer_connect: Don't explicitly connect on construction - wait for connect call.\n",
      "        (default: False)\n",
      "    :param auth_plugin_map: A dict of plugin names to a class that processes that plugin.\n",
      "        The class will take the Connection object as the argument to the constructor.\n",
      "        The class needs an authenticate method taking an authentication packet as\n",
      "        an argument.  For the dialog plugin, a prompt(echo, prompt) method can be used\n",
      "        (if no authenticate method) for returning a string from the user. (experimental)\n",
      "    :param server_public_key: SHA256 authentication plugin public key value. (default: None)\n",
      "    :param binary_prefix: Add _binary prefix on bytes and bytearray. (default: False)\n",
      "    :param compress: Not supported\n",
      "    :param named_pipe: Not supported\n",
      "    :param db: **DEPRECATED** Alias for database.\n",
      "    :param passwd: **DEPRECATED** Alias for password.\n",
      "\n",
      "    See `Connection <https://www.python.org/dev/peps/pep-0249/#connection-objects>`_ in the\n",
      "    specification.\n",
      "    \n",
      "\u001b[41m                     classess                     \u001b[0m\n",
      "\u001b[42mDataError\u001b[0m\n",
      "    Exception raised for errors that are due to problems with the\n",
      "        processed data like division by zero, numeric value out of range,\n",
      "        etc.\n",
      "\u001b[42mDatabaseError\u001b[0m\n",
      "    Exception raised for errors that are related to the\n",
      "        database.\n",
      "\u001b[42mError\u001b[0m\n",
      "    Exception that is the base class of all other error exceptions\n",
      "        (not Warning).\n",
      "\u001b[42mIntegrityError\u001b[0m\n",
      "    Exception raised when the relational integrity of the database\n",
      "        is affected, e.g. a foreign key check fails, duplicate key,\n",
      "        etc.\n",
      "\u001b[42mInterfaceError\u001b[0m\n",
      "    Exception raised for errors that are related to the database\n",
      "        interface rather than the database itself.\n",
      "\u001b[42mInternalError\u001b[0m\n",
      "    Exception raised when the database encounters an internal\n",
      "        error, e.g. the cursor is not valid anymore, the transaction is\n",
      "        out of sync, etc.\n",
      "\u001b[42mNotSupportedError\u001b[0m\n",
      "    Exception raised in case a method or database API was used\n",
      "        which is not supported by the database, e.g. requesting a\n",
      "        .rollback() on a connection that does not support transaction or\n",
      "        has transactions turned off.\n",
      "\u001b[42mOperationalError\u001b[0m\n",
      "    Exception raised for errors that are related to the database's\n",
      "        operation and not necessarily under the control of the programmer,\n",
      "        e.g. an unexpected disconnect occurs, the data source name is not\n",
      "        found, a transaction could not be processed, a memory allocation\n",
      "        error occurred during processing, etc.\n",
      "\u001b[42mProgrammingError\u001b[0m\n",
      "    Exception raised for programming errors, e.g. table not found\n",
      "        or already exists, syntax error in the SQL statement, wrong number\n",
      "        of parameters specified, etc.\n",
      "\u001b[42mWarning\u001b[0m\n",
      "    Exception raised for important warnings like data truncations\n",
      "        while inserting, etc.\n",
      "\u001b[42mcursorclass\u001b[0m\n",
      "\n",
      "        This is the object you use to interact with the database.\n",
      "\n",
      "        Do not create an instance of a Cursor yourself. Call\n",
      "        connections.Connection.cursor().\n",
      "\n",
      "        See `Cursor <https://www.python.org/dev/peps/pep-0249/#cursor-objects>`_ in\n",
      "        the specification.\n",
      "    \n",
      "\u001b[41m               function_or_methods                \u001b[0m\n",
      "\u001b[42maffected_rows\u001b[0m\n",
      "\n",
      "\u001b[42mautocommit\u001b[0m\n",
      "\n",
      "\u001b[42mbegin\u001b[0m\n",
      "    Begin transaction.\n",
      "\u001b[42mcharacter_set_name\u001b[0m\n",
      "\n",
      "\u001b[42mclose\u001b[0m\n",
      "\n",
      "            Send the quit message and close the socket.\n",
      "\n",
      "            See `Connection.close() <https://www.python.org/dev/peps/pep-0249/#Connection.close>`_\n",
      "            in the specification.\n",
      "\n",
      "            :raise Error: If the connection is already closed.\n",
      "        \n",
      "\u001b[42mcommit\u001b[0m\n",
      "\n",
      "            Commit changes to stable storage.\n",
      "\n",
      "            See `Connection.commit() <https://www.python.org/dev/peps/pep-0249/#commit>`_\n",
      "            in the specification.\n",
      "        \n",
      "\u001b[42mconnect\u001b[0m\n",
      "\n",
      "\u001b[42mcursor\u001b[0m\n",
      "\n",
      "            Create a new cursor to execute queries with.\n",
      "\n",
      "            :param cursor: The type of cursor to create; one of :py:class:`Cursor`,\n",
      "                :py:class:`SSCursor`, :py:class:`DictCursor`, or :py:class:`SSDictCursor`.\n",
      "                None means use Cursor.\n",
      "        \n",
      "\u001b[42mescape\u001b[0m\n",
      "    Escape whatever value you pass to it.\n",
      "\n",
      "            Non-standard, for internal use; do not use this in your applications.\n",
      "        \n",
      "\u001b[42mescape_string\u001b[0m\n",
      "\n",
      "\u001b[42mget_autocommit\u001b[0m\n",
      "\n",
      "\u001b[42mget_host_info\u001b[0m\n",
      "\n",
      "\u001b[42mget_proto_info\u001b[0m\n",
      "\n",
      "\u001b[42mget_server_info\u001b[0m\n",
      "\n",
      "\u001b[42minsert_id\u001b[0m\n",
      "\n",
      "\u001b[42mkill\u001b[0m\n",
      "\n",
      "\u001b[42mliteral\u001b[0m\n",
      "    Alias for escape()\n",
      "\n",
      "            Non-standard, for internal use; do not use this in your applications.\n",
      "        \n",
      "\u001b[42mnext_result\u001b[0m\n",
      "\n",
      "\u001b[42mping\u001b[0m\n",
      "\n",
      "            Check if the server is alive.\n",
      "\n",
      "            :param reconnect: If the connection is closed, reconnect.\n",
      "            :raise Error: If the connection is closed and reconnect=False.\n",
      "        \n",
      "\u001b[42mquery\u001b[0m\n",
      "\n",
      "\u001b[42mrollback\u001b[0m\n",
      "\n",
      "            Roll back the current transaction.\n",
      "\n",
      "            See `Connection.rollback() <https://www.python.org/dev/peps/pep-0249/#rollback>`_\n",
      "            in the specification.\n",
      "        \n",
      "\u001b[42mselect_db\u001b[0m\n",
      "\n",
      "            Set current db.\n",
      "\n",
      "            :param db: The name of the db.\n",
      "        \n",
      "\u001b[42mset_charset\u001b[0m\n",
      "\n",
      "\u001b[42mshow_warnings\u001b[0m\n",
      "    Send the \"SHOW WARNINGS\" SQL command.\n",
      "\u001b[42mthread_id\u001b[0m\n",
      "\n",
      "\u001b[42mwrite_packet\u001b[0m\n",
      "    Writes an entire \"mysql packet\" in its entirety to the network\n",
      "            adding its length and sequence number.\n",
      "        \n",
      "\u001b[41m                    constants                     \u001b[0m\n",
      "\u001b[42mautocommit_mode\u001b[0m\n",
      "    False\n",
      "\u001b[42mbind_address\u001b[0m\n",
      "    None\n",
      "\u001b[42mcharset\u001b[0m\n",
      "    'utf8mb4'\n",
      "\u001b[42mclient_flag\u001b[0m\n",
      "    3842573\n",
      "\u001b[42mconnect_timeout\u001b[0m\n",
      "    10\n",
      "\u001b[42mdb\u001b[0m\n",
      "    b'shop'\n",
      "\u001b[42mdecoders\u001b[0m\n",
      "    {16: <function through at 0x0000017A0BAC4F28>, 1: <class 'int'>, 2: <class 'int'>, 3: <class 'int'>, 4: <class 'float'>, 5: <class 'float'>, 8: <class 'int'>, 9: <class 'int'>, 13: <class 'int'>, 7: <function convert_datetime at 0x0000017A0BAC4D08>, 12: <function convert_datetime at 0x0000017A0BAC4D08>, 11: <function convert_timedelta at 0x0000017A0BAC4D90>, 10: <function convert_date at 0x0000017A0BAC4EA0>, 252: <function through at 0x0000017A0BAC4F28>, 249: <function through at 0x0000017A0BAC4F28>, 250: <function through at 0x0000017A0BAC4F28>, 251: <function through at 0x0000017A0BAC4F28>, 254: <function through at 0x0000017A0BAC4F28>, 253: <function through at 0x0000017A0BAC4F28>, 15: <function through at 0x0000017A0BAC4F28>, 0: <class 'decimal.Decimal'>, 246: <class 'decimal.Decimal'>}\n",
      "\u001b[42mencoders\u001b[0m\n",
      "    {<class 'bool'>: <function escape_bool at 0x0000017A0BAC4510>, <class 'int'>: <function escape_int at 0x0000017A0BAC4598>, <class 'float'>: <function escape_float at 0x0000017A0BAC4620>, <class 'str'>: <function escape_str at 0x0000017A0BAC4840>, <class 'bytes'>: <function escape_bytes at 0x0000017A0BAC47B8>, <class 'tuple'>: <function escape_sequence at 0x0000017A0BAC4400>, <class 'list'>: <function escape_sequence at 0x0000017A0BAC4400>, <class 'set'>: <function escape_sequence at 0x0000017A0BAC4400>, <class 'frozenset'>: <function escape_sequence at 0x0000017A0BAC4400>, <class 'dict'>: <function escape_dict at 0x0000017A0BAC4378>, <class 'NoneType'>: <function escape_None at 0x0000017A0BAC48C8>, <class 'datetime.date'>: <function escape_date at 0x0000017A0BAC4AE8>, <class 'datetime.datetime'>: <function escape_datetime at 0x0000017A0BAC4A60>, <class 'datetime.timedelta'>: <function escape_timedelta at 0x0000017A0BAC4950>, <class 'datetime.time'>: <function escape_time at 0x0000017A0BAC49D8>, <class 'time.struct_time'>: <function escape_struct_time at 0x0000017A0BAC4B70>, <class 'decimal.Decimal'>: <function Decimal2Literal at 0x0000017A0BAC4BF8>}\n",
      "\u001b[42mencoding\u001b[0m\n",
      "    'utf8'\n",
      "\u001b[42mhost\u001b[0m\n",
      "    'localhost'\n",
      "\u001b[42mhost_info\u001b[0m\n",
      "    'socket localhost:3306'\n",
      "\u001b[42minit_command\u001b[0m\n",
      "    None\n",
      "\u001b[42mmax_allowed_packet\u001b[0m\n",
      "    16777216\n",
      "\u001b[42mopen\u001b[0m\n",
      "    True\n",
      "\u001b[42mpassword\u001b[0m\n",
      "    b'root'\n",
      "\u001b[42mport\u001b[0m\n",
      "    3306\n",
      "\u001b[42mprotocol_version\u001b[0m\n",
      "    10\n",
      "\u001b[42msalt\u001b[0m\n",
      "    b'^\\x01D\\x0e\\tI#a)%zH\\x02`\\r*LY&4'\n",
      "\u001b[42mserver_capabilities\u001b[0m\n",
      "    3355443199\n",
      "\u001b[42mserver_charset\u001b[0m\n",
      "    'utf8mb4'\n",
      "\u001b[42mserver_language\u001b[0m\n",
      "    255\n",
      "\u001b[42mserver_public_key\u001b[0m\n",
      "    None\n",
      "\u001b[42mserver_status\u001b[0m\n",
      "    0\n",
      "\u001b[42mserver_thread_id\u001b[0m\n",
      "    (115,)\n",
      "\u001b[42mserver_version\u001b[0m\n",
      "    '8.0.22'\n",
      "\u001b[42msql_mode\u001b[0m\n",
      "    None\n",
      "\u001b[42mssl\u001b[0m\n",
      "    False\n",
      "\u001b[42munix_socket\u001b[0m\n",
      "    None\n",
      "\u001b[42muse_unicode\u001b[0m\n",
      "    True\n",
      "\u001b[42muser\u001b[0m\n",
      "    b'root'\n",
      "None\n",
      "\n"
     ]
    }
   ],
   "source": [
    "aprint(adoc(conn))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## cursor"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### classify(cur)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "If it is a callable object then you can assign a __name__ attribute to it.\n",
      "But the value assigned to __name__ must be the name of the callable object.\n",
      "If it is a callable object then you can assign a __name__ attribute to it.\n",
      "But the value assigned to __name__ must be the name of the callable object.\n",
      "If it is a callable object then you can assign a __name__ attribute to it.\n",
      "But the value assigned to __name__ must be the name of the callable object.\n",
      "\u001b[41m                     classess                     \u001b[0m\n",
      "\u001b[41m001\u001b[0m DataError                     \u001b[41m002\u001b[0m DatabaseError                 \u001b[41m003\u001b[0m Error                         \u001b[41m004\u001b[0m IntegrityError                \n",
      "\u001b[41m005\u001b[0m InterfaceError                \u001b[41m006\u001b[0m InternalError                 \u001b[41m007\u001b[0m NotSupportedError             \u001b[41m008\u001b[0m OperationalError              \n",
      "\u001b[41m009\u001b[0m ProgrammingError              \u001b[41m010\u001b[0m Warning                       \n",
      "\u001b[41m               function_or_methods                \u001b[0m\n",
      "\u001b[41m001\u001b[0m callproc                      \u001b[41m002\u001b[0m close                         \u001b[41m003\u001b[0m execute                       \u001b[41m004\u001b[0m executemany                   \n",
      "\u001b[41m005\u001b[0m fetchall                      \u001b[41m006\u001b[0m fetchmany                     \u001b[41m007\u001b[0m fetchone                      \u001b[41m008\u001b[0m mogrify                       \n",
      "\u001b[41m009\u001b[0m nextset                       \u001b[41m010\u001b[0m scroll                        \u001b[41m011\u001b[0m setinputsizes                 \u001b[41m012\u001b[0m setoutputsizes                \n",
      "\n",
      "\u001b[41m                    constants                     \u001b[0m\n",
      "\u001b[41m001\u001b[0m arraysize                     \u001b[41m002\u001b[0m connection                    \u001b[41m003\u001b[0m description                   \u001b[41m004\u001b[0m lastrowid                     \n",
      "\u001b[41m005\u001b[0m max_stmt_length               \u001b[41m006\u001b[0m rowcount                      \u001b[41m007\u001b[0m rownumber                     \n",
      "\n"
     ]
    }
   ],
   "source": [
    "aprint(classify(cursor))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.callproc()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(('id', 3, None, 11, 11, 0, False),\n",
       " ('first_name', 254, None, 80, 80, 0, False),\n",
       " ('last_name', 254, None, 80, 80, 0, True),\n",
       " ('age', 3, None, 11, 11, 0, True),\n",
       " ('sex', 254, None, 4, 4, 0, True),\n",
       " ('income', 4, None, 12, 12, 31, True))"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# .description\n",
    "# This read-only attribute is a sequence of 7-item sequences.\n",
    "\n",
    "# Each of these sequences contains information describing one result column:\n",
    "\n",
    "# name\n",
    "# type_code\n",
    "# display_size\n",
    "# internal_size\n",
    "# precision\n",
    "# scale\n",
    "# null_ok\n",
    "cursor.description"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'INSERT INTO Employee (first_name, last_name,age, sex, income)\\n                    VALUES    (%s, %s, %s, %s, %s)'"
      ]
     },
     "execution_count": 156,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.mogrify(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.nextset()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.scroll(1, mode='absolute')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### adoc(cur)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "If it is a callable object then you can assign a __name__ attribute to it.\n",
      "But the value assigned to __name__ must be the name of the callable object.\n",
      "If it is a callable object then you can assign a __name__ attribute to it.\n",
      "But the value assigned to __name__ must be the name of the callable object.\n",
      "If it is a callable object then you can assign a __name__ attribute to it.\n",
      "But the value assigned to __name__ must be the name of the callable object.\n",
      "\u001b[41m<pymysql.cursors.Cursor object at 0x0000017A0E38FA20>\u001b[0m \n",
      "    This is the object you use to interact with the database.\n",
      "\n",
      "    Do not create an instance of a Cursor yourself. Call\n",
      "    connections.Connection.cursor().\n",
      "\n",
      "    See `Cursor <https://www.python.org/dev/peps/pep-0249/#cursor-objects>`_ in\n",
      "    the specification.\n",
      "    \n",
      "\u001b[41m                     classess                     \u001b[0m\n",
      "\u001b[42mDataError\u001b[0m\n",
      "    Exception raised for errors that are due to problems with the\n",
      "        processed data like division by zero, numeric value out of range,\n",
      "        etc.\n",
      "\u001b[42mDatabaseError\u001b[0m\n",
      "    Exception raised for errors that are related to the\n",
      "        database.\n",
      "\u001b[42mError\u001b[0m\n",
      "    Exception that is the base class of all other error exceptions\n",
      "        (not Warning).\n",
      "\u001b[42mIntegrityError\u001b[0m\n",
      "    Exception raised when the relational integrity of the database\n",
      "        is affected, e.g. a foreign key check fails, duplicate key,\n",
      "        etc.\n",
      "\u001b[42mInterfaceError\u001b[0m\n",
      "    Exception raised for errors that are related to the database\n",
      "        interface rather than the database itself.\n",
      "\u001b[42mInternalError\u001b[0m\n",
      "    Exception raised when the database encounters an internal\n",
      "        error, e.g. the cursor is not valid anymore, the transaction is\n",
      "        out of sync, etc.\n",
      "\u001b[42mNotSupportedError\u001b[0m\n",
      "    Exception raised in case a method or database API was used\n",
      "        which is not supported by the database, e.g. requesting a\n",
      "        .rollback() on a connection that does not support transaction or\n",
      "        has transactions turned off.\n",
      "\u001b[42mOperationalError\u001b[0m\n",
      "    Exception raised for errors that are related to the database's\n",
      "        operation and not necessarily under the control of the programmer,\n",
      "        e.g. an unexpected disconnect occurs, the data source name is not\n",
      "        found, a transaction could not be processed, a memory allocation\n",
      "        error occurred during processing, etc.\n",
      "\u001b[42mProgrammingError\u001b[0m\n",
      "    Exception raised for programming errors, e.g. table not found\n",
      "        or already exists, syntax error in the SQL statement, wrong number\n",
      "        of parameters specified, etc.\n",
      "\u001b[42mWarning\u001b[0m\n",
      "    Exception raised for important warnings like data truncations\n",
      "        while inserting, etc.\n",
      "\u001b[41m               function_or_methods                \u001b[0m\n",
      "\u001b[42mcallproc\u001b[0m\n",
      "    Execute stored procedure procname with args\n",
      "\n",
      "            procname -- string, name of procedure to execute on server\n",
      "\n",
      "            args -- Sequence of parameters to use with procedure\n",
      "\n",
      "            Returns the original args.\n",
      "\n",
      "            Compatibility warning: PEP-249 specifies that any modified\n",
      "            parameters must be returned. This is currently impossible\n",
      "            as they are only available by storing them in a server\n",
      "            variable and then retrieved by a query. Since stored\n",
      "            procedures return zero or more result sets, there is no\n",
      "            reliable way to get at OUT or INOUT parameters via callproc.\n",
      "            The server variables are named @_procname_n, where procname\n",
      "            is the parameter above and n is the position of the parameter\n",
      "            (from zero). Once all result sets generated by the procedure\n",
      "            have been fetched, you can issue a SELECT @_procname_0, ...\n",
      "            query using .execute() to get any OUT or INOUT values.\n",
      "\n",
      "            Compatibility warning: The act of calling a stored procedure\n",
      "            itself creates an empty result set. This appears after any\n",
      "            result sets generated by the procedure. This is non-standard\n",
      "            behavior with respect to the DB-API. Be sure to use nextset()\n",
      "            to advance through all result sets; otherwise you may get\n",
      "            disconnected.\n",
      "        \n",
      "\u001b[42mclose\u001b[0m\n",
      "\n",
      "            Closing a cursor just exhausts all remaining data.\n",
      "        \n",
      "\u001b[42mexecute\u001b[0m\n",
      "    Execute a query\n",
      "\n",
      "            :param str query: Query to execute.\n",
      "\n",
      "            :param args: parameters used with query. (optional)\n",
      "            :type args: tuple, list or dict\n",
      "\n",
      "            :return: Number of affected rows\n",
      "            :rtype: int\n",
      "\n",
      "            If args is a list or tuple, %s can be used as a placeholder in the query.\n",
      "            If args is a dict, %(name)s can be used as a placeholder in the query.\n",
      "        \n",
      "\u001b[42mexecutemany\u001b[0m\n",
      "    Run several data against one query\n",
      "\n",
      "            :param query: query to execute on server\n",
      "            :param args:  Sequence of sequences or mappings.  It is used as parameter.\n",
      "            :return: Number of rows affected, if any.\n",
      "\n",
      "            This method improves performance on multiple-row INSERT and\n",
      "            REPLACE. Otherwise it is equivalent to looping over args with\n",
      "            execute().\n",
      "        \n",
      "\u001b[42mfetchall\u001b[0m\n",
      "    Fetch all the rows\n",
      "\u001b[42mfetchmany\u001b[0m\n",
      "    Fetch several rows\n",
      "\u001b[42mfetchone\u001b[0m\n",
      "    Fetch the next row\n",
      "\u001b[42mmogrify\u001b[0m\n",
      "\n",
      "            Returns the exact string that is sent to the database by calling the\n",
      "            execute() method.\n",
      "\n",
      "            This method follows the extension to the DB API 2.0 followed by Psycopg.\n",
      "        \n",
      "\u001b[42mnextset\u001b[0m\n",
      "\n",
      "\u001b[42mscroll\u001b[0m\n",
      "\n",
      "\u001b[42msetinputsizes\u001b[0m\n",
      "    Does nothing, required by DB API.\n",
      "\u001b[42msetoutputsizes\u001b[0m\n",
      "    Does nothing, required by DB API.\n",
      "\u001b[41m                    constants                     \u001b[0m\n",
      "\u001b[42marraysize\u001b[0m\n",
      "    1\n",
      "\u001b[42mconnection\u001b[0m\n",
      "    <pymysql.connections.Connection object at 0x0000017A0E38FC50>\n",
      "\u001b[42mdescription\u001b[0m\n",
      "    (('id', 3, None, 11, 11, 0, False), ('first_name', 254, None, 80, 80, 0, False), ('last_name', 254, None, 80, 80, 0, True), ('age', 3, None, 11, 11, 0, True), ('sex', 254, None, 4, 4, 0, True), ('income', 4, None, 12, 12, 31, True))\n",
      "\u001b[42mlastrowid\u001b[0m\n",
      "    None\n",
      "\u001b[42mmax_stmt_length\u001b[0m\n",
      "    1024000\n",
      "\u001b[42mrowcount\u001b[0m\n",
      "    6\n",
      "\u001b[42mrownumber\u001b[0m\n",
      "    6\n",
      "None\n",
      "\n"
     ]
    }
   ],
   "source": [
    "aprint(adoc(cursor))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# with statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(17, 'Dillon', 'Leung', 20, 'M', 4000.0)\n",
      "(18, 'Dillon', 'Leung', 20, 'M', 5000.0)\n",
      "(19, 'Dillon', 'Leung', 20, 'M', 6000.0)\n",
      "(20, 'Dillon', 'Leung', 20, 'M', 4000.0)\n",
      "(21, 'Dillon', 'Leung', 20, 'M', 5000.0)\n",
      "(22, 'Dillon', 'Leung', 20, 'M', 6000.0)\n",
      "(23, 'Dillon', 'Leung', 20, 'M', 4000.0)\n",
      "(24, 'Dillon', 'Leung', 20, 'M', 5000.0)\n",
      "(25, 'Dillon', 'Leung', 20, 'M', 6000.0)\n",
      "(26, 'Dillon', 'Leung', 20, 'M', 4000.0)\n",
      "(27, 'Dillon', 'Leung', 20, 'M', 5000.0)\n",
      "(28, 'Dillon', 'Leung', 20, 'M', 6000.0)\n"
     ]
    }
   ],
   "source": [
    "import pymysql\n",
    "\n",
    "connection = pymysql.connect(host='localhost',\n",
    "                            port=3306,\n",
    "                            user='root',\n",
    "                            password='root',\n",
    "                            database='shop',\n",
    "                            charset='utf8mb4')\n",
    "\n",
    "with connection:\n",
    "    with connection.cursor() as cursor:\n",
    "        sql = \"\"\"INSERT INTO Employee (first_name, last_name,age, sex, income)\n",
    "                            VALUES    (%s, %s, %s, %s, %s)\"\"\"\n",
    "\n",
    "\n",
    "        data = [(\"Dillon\", \"Leung\", 20, \"M\", 4000),\n",
    "                (\"Dillon\", \"Leung\", 20, \"M\", 5000),\n",
    "                (\"Dillon\", \"Leung\", 20, \"M\", 6000)]\n",
    "        cursor.executemany(sql, data)\n",
    "        connection.commit()\n",
    "        \n",
    "        with connection.cursor() as cursor:\n",
    "            sql = \"\"\"SELECT id, first_name, last_name,age, sex, income FROM Employee\"\"\"\n",
    "            cursor.execute(sql)\n",
    "            results = cursor.fetchall()\n",
    "            for row in results:\n",
    "                print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
